package occ.service
{
  import flash.data.*;
  import flash.events.*;
  import flash.events.EventDispatcher;
  import flash.events.IEventDispatcher;
  
  import occ.model.*;
  
  import mx.collections.ArrayCollection;
  import mx.utils.*;
  
  import occ.utils.*;
  

  public class Sites extends EventDispatcher
  {
    public static const NULL_SITE_TYPE_ID:int=-1; 
    public static const GET_SITE_TYPES:String="GET_SITE_TYPES_EVENT";
    public static const GET_SITE_TYPE:String="GET_SITE_TYPE_EVENT";
    public static const SAVE_SITE_TYPE_RESULT:String="SAVE_SITE_TYPE_EVENT";
    public static const GET_SITES:String="GET_SITES_EVENT";
    public static const GET_SITE:String="GET_SITE_EVENT";
    public static const SAVE_SITE_RESULT:String="SAVE_SITE_EVENT";
    public static const SITE_NOT_FOUND:String="SiteResultReturnedNothing";
    
    
    
    private var sqlData:Array;
		private var stmt:SQLStatement;
		private var sql:String;
    private var getLastSiteTypeId:Boolean;
		
		public var siteTypes:ArrayCollection;
		public var siteType:SiteTypeVO;
		public var lastSiteTypeId:int;
    public var sites:ArrayCollection;
    public var site:SiteVO;
		
		
    public function Sites(target:IEventDispatcher=null)
    {
      super(target);
    }
    
    public function getSiteTypes():void
    {
      //trace("getSiteTypes");
      sql="select * from site_types where active_flag ='Y'";
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onGetSiteTypes );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onGetSiteTypes(event:SQLEvent):void
    {
      var result:SQLResult = stmt.getResult();
     // trace("look at onGetSiteTypes result");
     // trace(ObjectUtil.toString(result));
			siteTypes= new ArrayCollection();
      try{
        sqlData = result.data;
        for(var i:int=0;i<sqlData.length;i++){
         // trace("sqlData[i]: " + (sqlData[i] as Object).site_type_name);
          var s:SiteTypeVO = getSiteTypeFromResultSet(sqlData[i]);
         // trace("s.name: " + s.siteTypeName);
			    siteTypes.addItem( s);
			  }
      }catch(e:Error){}
      
			
			// trace("siteTypes: " + siteTypes.length);
			dispatchEvent(new Event(GET_SITE_TYPES));
			 
    }
    
    public function getSiteType(i:int):void
    {
       sql="select * from site_types where site_type_id = " + i;
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onGetSiteType );
			stmt.text = sql;
			stmt.execute();
    }
    
   
    
    private function onGetSiteType(event:SQLEvent):void
    {
      var result:SQLResult = stmt.getResult();
			for(var i:int=0;i<sqlData.length;i++){
			  siteType = getSiteTypeFromResultSet(sqlData[i] as Object);
			}
			 //trace(" the cnt: " + (sqlData[0] as Object).name);
			dispatchEvent(new Event(GET_SITE_TYPE));
    }
    
    private function getSiteTypeFromResultSet(o:Object):SiteTypeVO
    {
      
      var s:SiteTypeVO = new SiteTypeVO();
			s.siteTypeId = o.site_type_id;
			s.siteTypeName = o.site_type_name;
      
			s.siteTypeDescr = o.site_type_descr;
			s.dailyRateWeek=o.daily_rate_week;
      s.seasonRate=o.season_rate;
      s.dailyDefaultGuestCount=o.default_guest_count;
      s.dailyAddGuestRate=o.add_guest_rate;
      s.dailyRateWeekend=o.daily_rate_weekend;
      s.activeFlag=o.active_flag;
      s.monthAddGuestRate = o.month_add_guest_rate;
      s.monthDefaultGuestCount = o.month_default_guest_count;
      s.monthRate = o.month_rate;
      s.taxRate = o.tax_rate;
      s.rateDescr= "Weekends: " + s.dailyRateWeekend + " NonWeekends: " + s.dailyRateWeek + " Seasonal:" + s.seasonRate + " Monthly: " + s.monthRate;
      return s;
    }
    
    public function saveSiteType(stvo:SiteTypeVO):void
    {
      siteType = stvo;
     // trace("saveSiteType stvo: " + siteType.siteTypeId);
      if(stvo.siteTypeId == NULL_SITE_TYPE_ID){
        getLastSiteTypeId = true;
       // trace("We should be inserting")
        sql =" insert into site_types "+
            " (site_type_name, site_type_descr, daily_rate_week, season_rate, "+
            "  default_guest_count, add_guest_rate, daily_rate_weekend, active_flag, "+
            "  month_add_guest_rate,month_default_guest_count,month_rate, tax_rate) "+
            " values "+
            " ('" + siteType.siteTypeName + "', '" + siteType.siteTypeDescr + "'," + siteType.dailyRateWeek + ", " + siteType.seasonRate + ", "+ 
             + siteType.dailyDefaultGuestCount + ", " + siteType.dailyAddGuestRate + ", " + siteType.dailyRateWeekend + ", 'Y', "+
             siteType.monthAddGuestRate + "," + siteType.monthDefaultGuestCount + "," + siteType.monthRate + ", " + siteType.taxRate + ")";
        
      }else{
       sql=
       " update site_types set site_type_name='"+ siteType.siteTypeName +"', "+ 
       "   site_type_descr='"+ siteType.siteTypeDescr +"', "+
       "   daily_rate_week="+ siteType.dailyRateWeek +", "+
       "   season_rate="+siteType.seasonRate +",  "+
       "   default_guest_count="+siteType.dailyDefaultGuestCount +", "+ 
       "   add_guest_rate="+ siteType.dailyAddGuestRate+", "+
       "   daily_rate_weekend="+siteType.dailyRateWeekend +", "+ 
       "   active_flag='"+siteType.activeFlag +"', "+
       "   month_add_guest_rate="+siteType.monthAddGuestRate +", "+
       "   month_default_guest_count="+siteType.monthDefaultGuestCount +", "+
       "   month_rate="+siteType.monthRate + ", " +
       "   tax_rate =" + siteType.taxRate + 
      " where site_type_id= " + siteType.siteTypeId;
      }
      trace(sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onSaveSiteType );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onSaveSiteType(event:SQLEvent):void
    {
      if(getLastSiteTypeId){
        sql="Select last_insert_rowid() as result"; 
        stmt = new SQLStatement();
			  stmt.sqlConnection = DatabaseManager.getConnection();
        stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			  stmt.addEventListener( SQLEvent.RESULT, onGetLastSiteTypeId );
			  stmt.text = sql;
			  stmt.execute();
      }else
        dispatchEvent(new Event(SAVE_SITE_TYPE_RESULT));
    }
    
    private function onGetLastSiteTypeId(event:SQLEvent):void
    {
      //trace("onGetLastSiteTypeId");
      var newSiteTypeId:int=-1;
      var result:SQLResult = stmt.getResult();
      try{
        sqlData = result.data;
        for(var i:int=0;i<sqlData.length;i++){
  			  newSiteTypeId = (sqlData[i] as Object).result;
  			}
      }catch(e:Error){}
      
       //trace("newSiteTypeId: " + newSiteTypeId);
       siteType.siteTypeId = newSiteTypeId;
       dispatchEvent(new Event(SAVE_SITE_TYPE_RESULT));
    }
    
    public function saveSite(siteTypeId:int, svo:SiteVO):void
    {
      //trace("saveSite svo: " + svo.siteId);
      if(svo.siteId == SiteVO.NULL_SITE_ID){
        //trace("We should be inserting")
        sql =" insert into sites "+
            " (site_type_id, site_name, site_descr) "+
            " values "+
            " (" + siteTypeId + ", '" + svo.name + "','" + svo.descr + "')";
        
      }else{
       sql=
       " update sites set site_name='"+ svo.name +"', "+ 
       "   site_descr='"+ svo.descr +"' "+
      " where site_id= " + svo.siteId;
      }
      //trace(sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onSaveSite );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onSaveSite(event:SQLEvent):void
    {
      dispatchEvent(new Event(SAVE_SITE_RESULT));
    }
    
    public function getAllSites():void
    {
      //trace("getSites");
      sql="select * from sites s, site_types st "+
          " where s.active_flag='Y' "+
          " and s.site_type_id = st.site_type_id "+
          " order by st.site_type_name, site_descr";
      // trace("getSites sql : " + sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onGetSites );
			stmt.text = sql;
			stmt.execute();
    }
    
    public function getSites(i:int):void
    {
      //trace("getSites");
      sql="select * from sites where active_flag='Y' and site_type_id = " + i;
      // trace("getSites sql : " + sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onGetSites );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onGetSites(event:SQLEvent):void
    {
      //trace("onGetSites");
      
      var result:SQLResult = stmt.getResult();
      //trace("look at result");
      //trace(ObjectUtil.toString(result));
			sites= new ArrayCollection();
      try{
        sqlData = result.data;
        for(var i:int=0;i<sqlData.length;i++){
			  //trace("in the loop " +  (sqlData[i] as Object).site_name)
			  var o:SiteVO = new SiteVO();
			  o.siteId = (sqlData[i] as Object).site_id;
			  o.name = (sqlData[i] as Object).site_name;
			  o.descr = (sqlData[i] as Object).site_descr;
			  sites.addItem(o);
			}
      }catch(e:Error){}
      
			
			 trace("siteTypes: " + sites.length);
			dispatchEvent(new Event(GET_SITES));
			 
    }
    
     public function searchSite(s:String):void
    {
      trace("searchSite");
      sql=" select *  "+
          " from sites s, site_types st "+
          "  where s.active_flag='Y'  "+
          "   and st.active_flag='Y' "+
          "   and s.site_type_id = st.site_type_id "+
          " and upper(s.site_name) = upper('" + s + "') ";;
       trace("getSiteFromName sql : " + sql);
      stmt = new SQLStatement();
			stmt.sqlConnection = DatabaseManager.getConnection();
			
			stmt.addEventListener( SQLErrorEvent.ERROR, onSQLError );
			stmt.addEventListener( SQLEvent.RESULT, onSearchSite );
			stmt.text = sql;
			stmt.execute();
    }
    
    private function onSearchSite(event:SQLEvent):void
    {
      trace("onSearchSite");
      var reportGood:Boolean= true;
      var result:SQLResult = stmt.getResult();
      trace("look at result");
      trace(ObjectUtil.toString(result));
			site= new SiteVO();
      try{
        sqlData = result.data;
        
        if(sqlData.length ==0)
          reportGood = false;
          
        for(var i:int=0;i<sqlData.length;i++){
  			  trace("in the loop " +  (sqlData[i] as Object).site_name)
  			  site.siteId = (sqlData[i] as Object).site_id;
  			  site.name = (sqlData[i] as Object).site_name;
  			  site.descr = (sqlData[i] as Object).site_descr;
          site.siteType = getSiteTypeFromResultSet(sqlData[i] as Object);
  			}
      }catch(e:Error){
        reportGood = false;
      }
      
      if(reportGood)
   	    dispatchEvent(new Event(GET_SITE));
      else
        dispatchEvent(new Event(SITE_NOT_FOUND));
	  }
    
    private function onSQLError( event:SQLErrorEvent ):void
		{
			 var errorString:String = "SQL Error: " + event.error.errorID + "nDetails: " + event.error.details + "nDetail Arguments: " +event.error.detailArguments;
 			throw new Error(errorString);
		}
    
  }
}